<?php
/**
 * Description of Emms_Report_ActiveLoans
 *
 * @author andres
 */
class Emms_Report_ActiveLoans extends HTech_Report
{
    protected function _setName() 
    {
        $this->_name = $this->translate('Active loans');
    }
    
    protected function _setForm() 
    {
        $this->_form = new HTech_Report_Form_Date();
    }

    protected function _setIndex() {}

    protected function _setSql() 
    {
        $this->_sql = '
            select 
                l.LoanId,
                cy.Cycle LoanCycle,
                cybln.Cycle LoanCycleByLine,
                cybpg.Cycle LoanCycleByProgram,
                cybpd.Cycle LoanCycleByProduct,
                bt.Type BusinessType,
                bt.Activity BusinessActivity,
                l.ClientId,
                c.Code ClientCode,
                c.CodeType ClientCodeType,
                concat(c.FirstName, " ", c.LastName, " ", c.SecondLastName) ClientFullName,
                c.Birthdate ClientBirthdate,
                c.Gender ClientGender,
                c.Education ClientEducation,
                c.Handicap ClientHandicap,
                c.MaritalStatus ClientMaritalStatus,
                c.Children ClientChildren,
                c.Dependents ClientDependents,
                c.HomePhone ClientHomePhone,
                c.MobilePhone ClientMobilePhone,
                cn.Code ClientCountryCode,
                cn.Country ClientCountry,
                lm.CommunityId,
                cm.Type CommunityType,
                cm.Community,
                lm.BorrowerType,
                if(lm.BorrowerType = "B", lm.BorrowerId, 0) GroupId,
                if(lm.BorrowerType = "B", g.Name, "") GroupName,
                if(lm.BorrowerType = "B", g.BankId, 0) BankId,
                lc.Name LocationName,
                lc.ShortName LocationShortName,
                lc.Code LocationCode,
                lc.Category LocationCategory,
                lo.Username LoanOfficerUsername,
                co.Username CurrentOfficerUsername,
                f.Code FundCode,
                f.Fund FundName,
                f.Status FundCurrentStatus,
                pl.Code ProductLineCode,
                pl.ProductLine,
                pg.Program,
                pg.Status ProgramCurrentStatus,
                lm.LoanProductId,
                lp.Name LoanProduct,
                l.Installments,
                l.PaymentFrequency,
                l.InterestCalculation,
                l.EffectiveRate,
                l.AnnualPercentageRate,
                l.LoanInsurancePct,
                l.HealthInsurancePct,
                l.AdministrativeFeePct,
                l.DelayPenaltyRate,
                l.DelayPenaltyFixedCharge,
                l.DelayGrace,
                l.PrincipalGraceRate,
                l.Principal,
                l.LoanInsuranceMnt,
                l.HealthInsuranceMnt,
                l.AdministrativeFeeMnt,
                l.PaymentMnt,
                sum(p.Principal) Payments,
                l.Principal - sum(p.Principal) PrincipalBalance,
                lm.DeliveryDate,
                lm.ExpectedCancelDate,
                lsc3.Status StatusChangedTo,
                lsc3.Date StatusChangedOn,
                l.Status CurrentStatus 
            from
                loan_status_changes lsc1
            left join
                loan_status_changes lsc2 on 
                    lsc2.LoanId = lsc1.LoanId and 
                    lsc2.PreviousStatus = "Disbursed" and 
                    lsc2.Date <= :date
            left join
                loans l on l.LoanId = lsc1.LoanId
            left join
                loan_status_changes lsc3 on 
                    lsc3.LoanId = l.LoanId and 
                    lsc3.PreviousStatus = "Disbursed"
            left join
                payments p on p.LoanId = lsc1.LoanId and p.Date < :date
            left join
                loan_masters lm on lm.LoanMasterId = l.LoanMasterId
            left join
                business_types bt on bt.TypeId = l.BusinessTypeId
            left join
                clients c on c.ClientId = l.ClientId
            left join
                countries cn on cn.CountryId = c.CountryId
            left join
                communities cm on cm.CommunityId = lm.CommunityId
            left join
                groups g on g.GroupId = lm.BorrowerId
            left join
                locations lc on lc.LocationId = lm.LocationId
            left join
                users lo on lo.UserId = lm.OfficerUserId
            left join
                users co on co.UserId = c.OfficerUserId
            left join
                funds f on f.FundId = lm.FundId
            left join
                product_lines pl on pl.ProductLineId = lm.ProductLineId
            left join
                programs pg on pg.ProgramId = lm.ProgramId
            left join
                loan_products lp on lp.LoanProductId = lm.LoanProductId
            left join
                loan_cycles cy on cy.LoanId = l.LoanId
            left join
                loan_cycles_by_line cybln on cybln.LoanId = l.LoanId
            left join
                loan_cycles_by_program cybpg on cybpg.LoanId = l.LoanId
            left join
                loan_cycles_by_product cybpd on cybpd.LoanId = l.LoanId
            where
                lsc1.Status = "Disbursed" and
                lsc1.Date <= :date and
                lsc2.Id is null
            group by
                lsc1.LoanId';
    }
    
    protected function _setHeaders() 
    {
        $this->_headers = new stdClass();
        $this->_headers->LoanId = $this->translate('Loan Id');
        $this->_headers->LoanCycle = $this->translate('Loan cycle');
        $this->_headers->LoanCycleByLine = $this->translate('Loan cycle by line');
        $this->_headers->LoanCycleByProgram = $this->translate('Loan cycle by program');
        $this->_headers->LoanCycleByProduct = $this->translate('Loan cycle by product');
        $this->_headers->BusinessType = $this->translate('Business type');
        $this->_headers->BusinessActivity = $this->translate('Business activity');
        $this->_headers->ClientId = $this->translate('Client Id');
        $this->_headers->ClientCode = $this->translate('Client code');
        $this->_headers->ClientCodeType = $this->translate('Code type');
        $this->_headers->ClientFullName = $this->translate('Name');
        $this->_headers->ClientBirthdate = $this->translate('Birthdate');
        $this->_headers->ClientGender = $this->translate('Gender');
        $this->_headers->ClientHandicap = $this->translate('Handicap');
        $this->_headers->ClientHomePhone = $this->translate('ClientHomePhone');
        $this->_headers->ClientMobilePhone = $this->translate('ClientMobilePhone');
        $this->_headers->ClientCountry = $this->translate('Country');
        $this->_headers->ClientCountryCode = $this->translate('Country code');
        $this->_headers->CommunityId = $this->translate('Community Id');
        $this->_headers->CommunityType = $this->translate('Community type');
        $this->_headers->Community = $this->translate('Community');
        $this->_headers->BorrowerType = $this->translate('Borrower type');
        $this->_headers->GroupId = $this->translate('Group Id');
        $this->_headers->GroupName = $this->translate('Group name');
        $this->_headers->BankId = $this->translate('Bank Id');
        $this->_headers->LocationName = $this->translate('Location name');
        $this->_headers->LocationShortName = $this->translate('Location shortname');
        $this->_headers->LocationCode = $this->translate('Location code');
        $this->_headers->LocationCategory = $this->translate('Location category');
        $this->_headers->LoanOfficerUsername = $this->translate('Officer');
        $this->_headers->CurrentOfficerUsername = $this->translate('Current officer');
        $this->_headers->FundCode = $this->translate('Fund code');
        $this->_headers->FundName = $this->translate('Fund');
        $this->_headers->FundCurrentStatus = $this->translate('Fund status');
        $this->_headers->ProductLineCode = $this->translate('Line code');
        $this->_headers->ProductLine = $this->translate('Line');
        $this->_headers->Program = $this->translate('Program');
        $this->_headers->ProgramCurrentStatus = $this->translate('Program status');
        $this->_headers->LoanProductId = $this->translate('Product Id');
        $this->_headers->LoanProduct = $this->translate('Product');
        $this->_headers->Installments = $this->translate('Installments');
        $this->_headers->PaymentFrequency = $this->translate('Payment freq.');
        $this->_headers->InterestCalculation = $this->translate('Interest calc.');
        $this->_headers->EffectiveRate = $this->translate('Effective rate');
        $this->_headers->AnnualPercentageRate = $this->translate('Annual rate');
        $this->_headers->LoanInsurancePct = $this->translate('Loan insurance');
        $this->_headers->HealthInsurancePct = $this->translate('Health insurance');
        $this->_headers->AdministrativeFeePct = $this->translate('Admin. fee');
        $this->_headers->DelayGrace = $this->translate('Delay grace');
        $this->_headers->DelayPenaltyRate = $this->translate('Penalty rate');
        $this->_headers->DelayPenaltyFixedCharge = $this->translate('Penalty fixed charge');
        $this->_headers->PrincipalGraceRate = $this->translate('Principal grace rate');
        $this->_headers->Principal = $this->translate('Principal');
        $this->_headers->LoanInsuranceMnt = $this->translate('Loan insurance amount');
        $this->_headers->HealthInsuranceMnt = $this->translate('Health insurance amount');
        $this->_headers->AdministrativeFeeMnt = $this->translate('Admin. fee amount');
        $this->_headers->PaymentMnt = $this->translate('Payment');
        $this->_headers->PrincipalBalance = $this->translate('Principal balance');
        $this->_headers->DeliveryDate = $this->translate('Disbursement date');
        $this->_headers->ExpectedCancelDate = $this->translate('Expected paid date');
        $this->_headers->StatusChangedTo = $this->translate('Status change');
        $this->_headers->StatusChangedOn = $this->translate('Status change date');
        $this->_headers->CurrentStatus = $this->translate('Current status');
    }
    
    public function setParams($params) 
    {
        $this->_params = array(':date' => $params['Date']);
    }

}
